﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;

using System.Data.SqlClient;
using NCRGEA.AAL;
using NCRGEA.UserControls;

namespace NCRGEA.Members
{
    public partial class frmFullQuery : Form
    {
        #region Class Variables

        int sizePerEntry = 21;
        TextBox selectedList = new TextBox();
        
        #endregion

        public frmFullQuery()
        {
            InitializeComponent();
            loadData();
        }

        #region Control Manipulation

        /*
        /// <summary>
        /// This will make the calendar visible for the user to select a date
        /// </summary>
        /// <param name="sender"></param>
        private void loadCalendar(TextBox sender)
        {
            //calDatePick.Visible = true;
            selectedList = (TextBox)sender;
            //selectedList.Visible = false;
            calDatePick.Top = selectedList.Top + selectedList.Height - calDatePick.Height;
            calDatePick.Left = selectedList.Left;
        }*/

        /// <summary>
        /// This opens the DisplayDate form
        /// </summary>
        /// <param name="cmd"></param>
        private bool openDisplayData(string query)
        {
            NCRGEA.UtilityForms.frmDisplayData displayData = new NCRGEA.UtilityForms.frmDisplayData(query, null);

            if (this.MdiParent == null)
                return false;

            NCRGEA.frmMain temp = (frmMain)this.MdiParent;

            if (temp != null)
                temp.LaunchForm(displayData);

            return true;
        }

        private string convertToTime(string Text)
        {
            return Text;
        }

        #endregion

        #region Data Manipulation

        /// <summary>
        /// Builds the string for the Query
        /// </summary>
        private string buildQuery()
        {
            List<string> whereClause = new List<string>();
            List<string> orClause = new List<string>();

            string query = @"SELECT
	                            mi.FirstName, mi.LastName, mi.SSN, mi.MemberID,mi.DOB, SUBSTRING(mt.Name,1,1) nameChar, DATEDIFF(YEAR,mi.DOB, GETDATE()) Age
                            FROM
	                            memInfo mi
		                            INNER JOIN
			                            memTypes mt
		                            ON
			                            mt.TypeID = mi.TypeID";
            try
            {
                //Add clause for the pay date search
                if (ucPayDateStart.getText() != string.Empty || ucPayDateEnd.getText() != string.Empty)
                {
                    query = @"SELECT DISTINCT TOP 100
	                            mi.FirstName, mi.LastName, mi.SSN, mi.MemberID,mi.DOB, SUBSTRING(mt.Name,1,1) nameChar, DATEDIFF(YEAR,mi.DOB, GETDATE()) Age
                            FROM
	                            memInfo mi
		                            INNER JOIN
			                            memTypes mt
		                            ON
			                            mt.TypeID = mi.TypeID
                                            INNER JOIN
                                                Payments p
                                            ON
                                                p.MemberID = mi.MemberID";

                    Logger.Log("Paydate Search selected");
                }

                //Add SQL Statement WHERE clauses based on the text boxes
                if (txtAddress.Text != string.Empty)
                    whereClause.Add("\n mi.Address1 LIKE '" + txtAddress.Text + "%'");

                if (!string.IsNullOrWhiteSpace(txtAge.Text) && cBoxAge.SelectedIndex <= 0)
                {
                    whereClause.Add("\n DATEDIFF(YEAR,mi.DOB, GETDATE()) =" + txtAge.Text);
                    Logger.Log("Age will be selected as :" + txtAge.Text);
                }
                else if (!string.IsNullOrWhiteSpace(txtAge.Text) && cBoxAge.SelectedIndex > 0)
                {
                    string temp = string.Empty;

                    temp += "\n DATEDIFF(YEAR,mi.DOB, GETDATE()) ";

                    switch (cBoxAge.Text)
                    {
                        case "Greater Then":
                            temp += "> " + txtAge.Text;
                            break;
                        case "Less Then":
                            temp += "< " + txtAge.Text;
                            break;
                        case "Greater Then or Equal To":
                            temp += ">= " + txtAge.Text;
                            break;
                        case "Less Then or Equal To":
                            temp += "<= " + txtAge.Text;
                            break;
                    }

                    whereClause.Add(temp);
                    Logger.Log("Age will be selected as :" + temp);
                }

                //if(txtCity.Text != string.Empty)
                if (!string.IsNullOrWhiteSpace(txtCity.Text))
                {
                    whereClause.Add("\n mi.City LIKE '" + txtCity.Text + "%'");
                    Logger.Log("City added to whereClause: " + txtCity.Text);
                }

                if (!string.IsNullOrWhiteSpace(txtCounty.Text))
                {
                    whereClause.Add("\n mi.County LIKE '" + txtCounty.Text + "%'");
                    Logger.Log("County added to whereClause: " + txtCounty.Text);
                }

                if (!string.IsNullOrWhiteSpace(txtEmail.Text))
                {
                    whereClause.Add("\n mi.Email LIKE '" + txtEmail.Text + "%'");
                    Logger.Log("Email Added to whereClause: " + txtEmail.Text);
                }

                if (!string.IsNullOrWhiteSpace(txtFirstName.Text))
                {
                    whereClause.Add("\n mi.FirstName LIKE '" + txtFirstName.Text + "%'");
                    Logger.Log("First Name added to whereClause: " + txtFirstName.Text);
                }

                if (!string.IsNullOrWhiteSpace(txtLastName.Text))
                {
                    whereClause.Add("\n mi.LastName LIKE '" + txtLastName.Text + "%'");
                    Logger.Log("Last Named added to whereClause: " + txtLastName.Text);
                }

                if (!string.IsNullOrWhiteSpace(txtMemberID.Text))
                {
                    whereClause.Add("\n mi.MemberID = " + txtMemberID.Text + "");
                    Logger.Log("Member ID added to whereClause: " + txtMemberID.Text);
                }

                if (!string.IsNullOrWhiteSpace(txtMiddleName.Text))
                {
                    whereClause.Add("\n mi.MiddleName LIKE '" + txtMiddleName.Text + "%'");
                    Logger.Log("Middle Name added to whereClause: " + txtMiddleName.Text);
                }

                if (!string.IsNullOrWhiteSpace(txtPhone.Text))
                {
                    whereClause.Add("\n mi.Phone LIKE '" + txtPhone.Text + "%'");
                    Logger.Log("Phone Number added to whereClause: " + txtPhone.Text);
                }

                if (!string.IsNullOrWhiteSpace(txtSSN.Text))
                {
                    whereClause.Add("\n mi.SSN = '" + txtSSN.Text + "'");
                    Logger.Log("SSN added to whereClause: " + txtSSN.Text);
                }

                if (!string.IsNullOrWhiteSpace(txtState.Text))
                {
                    whereClause.Add("\n mi.State = '" + txtState.Text + "'");
                    Logger.Log("State added to whereClause: " + txtState.Text);
                }

                if (!string.IsNullOrWhiteSpace(txtZip.Text))
                {
                    whereClause.Add("\n mi.Zip LIKE '" + txtZip.Text + "%'");
                    Logger.Log("Zip Code added to whereClause: " + txtZip.Text);
                }

                if (!string.IsNullOrWhiteSpace(txtZip4.Text))
                {
                    whereClause.Add("\n mi.ZipFour LIKE '" + txtZip4.Text + "%'");
                    Logger.Log("Zip Four added to whereClause: " + txtZip4.Text);
                }

                //Check Boxes
                foreach (String iChecked in chkBoxStatus.CheckedItems)
                {
                    orClause.Add("\nmt.Name = '" + iChecked.ToString() + "'");
                    Logger.Log("Status added to orClause: " + iChecked.ToString());
                }

                foreach (String iChecked in chkGroupList.CheckedItems)
                {
                    orClause.Add("\n mi.GroupCode = '" + iChecked.ToString() + "'");
                    Logger.Log("Group Code added to orClause: " + iChecked.ToString());
                }

                //Determine the date ranges if any
                string sTemp = string.Empty;
                string sTemp2 = string.Empty;

                if (ucDoB.getText() != string.Empty)
                {
                    sTemp = "\nmi.DOB = " + "'" + convertToTime(ucDoB.getText()) + "'";
                    whereClause.Add(sTemp);
                    Logger.Log("Where clause added: " + sTemp);
                }

                //Join Date
                if (ucDateJoinedStart.getText() != string.Empty && ucDateJoinedEnd.getText() == string.Empty)
                {
                    sTemp = "\nmi.JoinDate = '" + convertToTime(ucDateJoinedStart.getText()) + "'";
                    whereClause.Add(sTemp);
                    Logger.Log("Where Clause added:" + sTemp);
                }
                else if (ucDateJoinedStart.getText() != string.Empty && ucDateJoinedEnd.getText() != string.Empty)
                {
                    sTemp = "\nmi.JoinDate BETWEEN '" +  convertToTime(ucDateJoinedStart.getText()) + "'";
                    sTemp2 = " AND '" + convertToTime(ucDateJoinedEnd.getText()) + "'";

                    whereClause.Add(sTemp + sTemp2);
                    Logger.Log("Where Clause added: " + sTemp + sTemp2);
                }

                

                //Renewal Date
                if (ucRenewalDateStart.getText() != string.Empty && ucRenewalDateEnd.getText() == string.Empty)
                {
                    sTemp = "\nmi.JoinDate = '" + convertToTime(ucRenewalDateStart.getText()) + "'";
                    whereClause.Add(sTemp);
                    Logger.Log("Where Clause added:" + sTemp);
                }
                else if (ucRenewalDateStart.getText() != string.Empty && ucRenewalDateEnd.getText() != string.Empty)
                {
                    sTemp = "\nmi.JoinDate BETWEEN '" + convertToTime(ucRenewalDateStart.getText()) + "'";
                    sTemp2 = " AND '" + convertToTime(ucRenewalDateEnd.getText()) + "'";

                    whereClause.Add(sTemp + sTemp2);
                    Logger.Log("Where Clause added: " + sTemp + sTemp2);
                }

                //Retire date
                if (ucDateRetiredStart.getText() != string.Empty && ucDateRetiredEnd.getText() == string.Empty)
                {
                    sTemp = "\nmi.JoinDate = '" + convertToTime(ucDateRetiredStart.getText()) + "'";
                    whereClause.Add(sTemp);
                    Logger.Log("Where Clause added:" + sTemp);
                }
                else if (ucDateRetiredStart.getText() != string.Empty && ucDateRetiredEnd.getText() != string.Empty)
                {
                    sTemp = "\nmi.JoinDate BETWEEN '" + convertToTime(ucDateRetiredStart.getText()) + "'";
                    sTemp2 = " AND '" + convertToTime(ucDateRetiredEnd.getText()) + "'";

                    whereClause.Add(sTemp + sTemp2);
                    Logger.Log("Where Clause added: " + sTemp + sTemp2);
                }


                //Payment date(s)
                if (ucPayDateStart.getText() != string.Empty && ucPayDateEnd.getText() == string.Empty)
                {
                    sTemp = "\np.Date = '" + convertToTime(ucPayDateStart.getText()) + "'";
                    whereClause.Add(sTemp);
                    Logger.Log("Where Clause added:" + sTemp);
                }
                else if (ucPayDateStart.getText() != string.Empty && ucPayDateEnd.getText() != string.Empty)
                {
                    sTemp = "\np.Date BETWEEN '" + convertToTime(ucPayDateStart.getText()) + "'";
                    sTemp2 = " AND '" + convertToTime(ucPayDateEnd.getText()) + "'";

                    whereClause.Add(sTemp + sTemp2);
                    Logger.Log("Where Clause added: " + sTemp + sTemp2);
                }


                if (chkDeceased.Checked == true)
                {
                    whereClause.Add("\n mi.Deceased = 'True'");
                    Logger.Log("Deceased is True Added");
                }

                if (dBoxSex.SelectedIndex > 0)
                {
                    whereClause.Add("\n mi.Sex = '" + dBoxSex.Text + "'");
                    Logger.Log("Sex added is: " + dBoxSex.Text);
                }

                if (whereClause.Count > 0 || orClause.Count > 0)
                    query += "\n WHERE \n";
                else
                    return query;

                //Add where clauses to the query if there are any
                for (int i = 0; i <= whereClause.Count - 1; i++)
                {
                    string temp = whereClause[i].ToString();

                    if (i > 0)
                    {
                        string final = " AND " + temp;
                        whereClause[i] = final;
                        Logger.Log("Added AND: " + final);
                    }
                    else
                    {
                        whereClause[i] = temp;
                        Logger.Log("Added AND: " + temp);
                    }
                }

                //Or clauses
                for (int i = 0; i <= orClause.Count - 1; i++)
                {
                    string temp = orClause[i].ToString();

                    if (i > 0)
                    {
                        string final = " OR " + temp;
                        orClause[i] = final;
                        Logger.Log("Added OR: " + final);
                    }
                    else
                    {
                        orClause[i] = temp;
                        Logger.Log("Added OR: " + temp);
                    }
                }

                //Add the Where Clauses
                for(int i = 0; i <= whereClause.Count - 1;i++)
                {
                    query += whereClause[i].ToString();
                    Logger.Log("Query Part Added: " + whereClause[i].ToString());
                }

                //Add the OR clauses
                for (int i = 0; i <= orClause.Count - 1; i++)
                {
                    query += orClause[i].ToString();
                    Logger.Log("Query Part Added: " + orClause[i].ToString());
                }
            }
            catch (Exception e)
            {
                Logger.Log("An error has occured: " + e.Message);
                MessageBox.Show("An error has occured!", "ERROR");
                return null;
            }
            Logger.Log("Query Created sucessfully.");
            Logger.Log("QUERY: " + query);
            return query;
        }

        /// <summary>
        /// Load the data from the DataBases
        /// </summary>
        private void loadData()
        {
            string query = @"SELECT
                                mt.Name
                             FROM
                                memTypes mt";

            DataTable temp = DAL.ExecuteQuery(query);

            Logger.Log("Status query: " + query);

            foreach (DataRow row in temp.Rows)
            {
                chkBoxStatus.Items.Add(row["Name"].ToString());
                Logger.Log("Item Loaded into Status Box: " + row["Name"].ToString());
            }

            query = @"SELECT
                        mg.GroupName
                    FROM
                        memGroup mg";

            Logger.Log("Status query: " + query);

            temp = DAL.ExecuteQuery(query);

            foreach (DataRow row in temp.Rows)
            {
                chkGroupList.Items.Add(row["GroupName"].ToString());
                Logger.Log("Item Loaded into Status Box: " + row["GroupName"].ToString());
            }
        }

        #endregion

        /// <summary>
        /// Scales the box equal to the total number of entries that there is
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void chkBoxStatus_MouseEnter(object sender, EventArgs e)
        {
            chkBoxStatus.Height = sizePerEntry * (chkBoxStatus.Items.Count - 1);
        }

        /// <summary>
        /// This makes the box size to just one entry.
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void chkBoxStatus_MouseLeave(object sender, EventArgs e)
        {
            chkBoxStatus.Height = sizePerEntry;
        }

        /// <summary>
        /// Validate the number of check boxes and then pass a datatable to the screen
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void btnSearch_Click(object sender, EventArgs e)
        {
            string query = buildQuery();

            if (query == string.Empty)
            {
                MessageBox.Show("There was an error while creating the query", "Query Error");
                return;
            }

            if (openDisplayData(query))
                this.Close();
        }

        private void chkGroupList_MouseEnter(object sender, EventArgs e)
        {
            chkGroupList.Height = sizePerEntry * (chkGroupList.Items.Count);
            chkGroupList.Width = this.Width - chkGroupList.Width;
        }

        private void chkGroupList_MouseLeave(object sender, EventArgs e)
        {
            chkGroupList.Height = sizePerEntry;
            chkGroupList.Width = 120;
        }

    }
}
